<?php
/**
 * Created by PhpStorm.
 * User: liuhongchuan
 * Date: 2018/5/24
 * Time: 15:59
 */
namespace app\cron\controller;

use think\Controller;
use think\Log;

class Import extends Controller
{
    public function _initialize()
    {
        $sk= input("sk/s");
        if ($sk != "27615e64890e433c74e9eb34f974d07e") {
            abort(404,'页面不存在');
        }
    }
    public function index()
    {
        $start_row = input("start_row/d", 2);
        $limit = input("limit/d", 10);

        //本地环境
        $shop_id = 122;
        $member_ship_id = 59;
        $admin_id = 97;

        //测试环境
//        $shop_id = 73;
//        $member_ship_id = 72;
//        $admin_id = 100;

        $file_name = 'test001.xlsx';
        $path = ROOT_PATH . 'data' . DS . 'excel' . DS . $file_name;
        if (!is_file($path)) {
            var_dump($path);
            exit();
        }
        if(strpos($path,'.xlsx')){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }

        $objPHPExcel = $objReader->load($path, $encode = 'utf-8');
        /*读取第0个工作表格*/
        $sheet = $objPHPExcel -> getSheet(0);
        /*取得总行数*/
        $highestRow = $sheet -> getHighestRow();
        /*取得列数*/
        $highestColumn = $sheet -> getHighestColumn();

        if ($start_row > $highestRow) {
            echo "开始行大于总行数";
            exit();
        }
        $AZ = array();
        for($i=65; $i<91; $i++){
            $AZ[]=strtoupper(chr($i));
        }
        /* 获取excel  标题*/
        for ($key=0;$key<26;$key++) {
            $cell_date=$objPHPExcel->getActiveSheet()->getCell($AZ[$key]."1")->getCalculatedValue();
            if(empty($cell_date)==FALSE){
                $excle_titles[]=$this->trimAll($cell_date."");
            }else{
                break;
            }
        }

        for ($i = $start_row; $i < ($start_row + $limit); $i++) {
            $data=array();
            foreach ($excle_titles as $key => $field) {
                $cell_date = $objPHPExcel->getActiveSheet()->getCell($AZ[$key+0].$i)->getCalculatedValue();

                    $data[$this->trimAll($field)] = $this->trimAll($cell_date);
            }
            $excle_data[] = $data;
            //$data['remark'] = $objPHPExcel -> getActiveSheet() -> getCell("P" . $i) -> ;
        }

//        if (empty($flag)){
//            exit();
//        }

        $cards = db("gym_card")->where(array("shop_id" => $shop_id, "status" => 1))->column("card_name,id,card_type,able_count,able_money,expiry_date,price");

        foreach ($excle_data as $key => $user) {
            if (empty($user))
            {
                continue;
            }

            $check_user = $this->checkUser($user, $cards);
            if (empty($check_user['flag'])) {
                Log::init([
                    'path'  =>  APP_PATH . 'logs/'
                ]);
                Log::write("err_col:" . $check_user["msg"] . ",data:" .json_encode($user, JSON_UNESCAPED_UNICODE),"err");
                continue;
            }
            $card_no = $user['会员卡号'];
            $card_is_exist = db("gym_order")->where(array("card_no" => $card_no, "shop_id" => $shop_id, "is_delete" => 0))->find();
            if (!empty($card_is_exist)) {
                continue;
            }
            $member_is_exist = db("gym_member")->where(array("shop_id" => $shop_id, "mobile" => $user["移动电话"]))->find();
            if (empty($member_is_exist)) {
                //入会
                $gym_member = array(
                    "name"              => $user["会员姓名"],
                    "sex"               => $user["性别"] == "男" ? 1 : 2,
                    "mobile"            => $user["移动电话"],
                    "descs"             => $user["备注说明"],
                    "come_from"         => "会籍派单",
                    "birthday"          => empty($user["会员生日"]) ? null : $user["会员生日"],
                    "member_ship_id"    => $member_ship_id,
                    "shop_id"           => $shop_id,
                    "web_open_up"       => 1,
                    "create_time"       => date("Y-m-d H:i:s", strtotime($user["加入时间"]))
                );
                $member_id = db("gym_member")->insertGetId($gym_member);
                //新增一条根据记录
                db("GymFollowUp")->insert(array("member_ship_id" => $member_ship_id, "member_id" => $member_id, "type" => 1, "follow_up_type" => 3, "shop_id" => $shop_id));
            } else {
                $gym_member = $member_is_exist;
                $member_id = $member_is_exist["id"];
            }


            //购卡
            $gym_card = $cards[$user["会员类型"]];

            //order数据
            $order_arr = array(
                "order_no"           => $this->getUniqueOrderNo(),
                "card_no"            => $card_no,
                "pay_money"          => $gym_card["price"],
                "pay_type"           => "其他",
                "come_from"          => "会籍派单",
                "order_date"         => $user["加入时间"],
                "activate_date"      => $user["开卡日期"],
                "start_date"         => $user["开卡日期"],
                "end_date"           => $user["到期日期"],
                "member_ship_id"     => $member_ship_id,
                "old_member_ship_id" => $member_ship_id,
                "shop_id"            => $shop_id,
                "descs"              => "",
                "gift_id"            => 0,
                "gift"               => "无",
                "gym_card_name"      => $gym_card["card_name"],
                "gym_card_type"      => $gym_card["card_type"],
                "able_count"         => $user["有效次数"],
                "able_day"           => $this->getAbleDay($user["开卡日期"], $user["到期日期"]),
                "able_money"         => $user["卡内余额"],
                "gym_card_id"        => $gym_card["id"],
                "admin_id"           => $admin_id,
                'order_status'       => 1,
                "gym_member_id"      => $member_id,
                "time_sets"          => empty($gym_card["time_sets"]) ? "" : $gym_card["time_sets"],
            );
            if (strtotime($user["开卡日期"]) > time()) {
                $order_arr["activate_type"] = 2;
                $order_arr['order_status'] = 3;
            }  else {
                $order_arr["activate_type"] = 1;
                $order_arr['order_status'] = 1;
            }


            $member_arr = array(
                "card_no"           => $card_no,
                "in_varifycation"   => 0,
                "start_date"        => $order_arr["start_date"],
                "end_date"          => $order_arr["end_date"],
                "mobile"            => $gym_member["mobile"],
                "contract_id"       => $order_arr["order_no"],
                "member_id"         => $member_id,
                "name"              => $gym_member["name"],
                "surplus_num"       => $order_arr["able_count"],
                "membership_type"   => $order_arr["gym_card_type"],
                "contract_status"   => $order_arr["order_status"],
                "owner_type"        => 1, //会员卡
                "shop_id"           => $shop_id,
            );
            $member_arr["in_varifycation_type"] = 0;







            //新增订单
            $order_id = db("gym_order")->insertGetId($order_arr);
            //新增购卡历史记录
            //历史记录
            $history_arr = array(
                "opt_type"          => "购卡",
                "order_no"          => $order_arr['order_no'],
                "card_no"           => $order_arr['card_no'],
                "shop_id"           => $shop_id,
                "gym_member_id"     => $order_arr['gym_member_id'],
                "admin_id"          => $admin_id,
                "gym_card_name"     => $order_arr['gym_card_name'],
                "gym_card_id"       => $order_arr['gym_card_id'],
                "gym_card_type"     => $order_arr['gym_card_type'],
                "pay_money"         => $order_arr['pay_money'],
                "pay_type"          => $order_arr['pay_type'],
                "order_date"        => $order_arr['order_date'],
                "member_ship_id"    => $order_arr['member_ship_id'],
                "descs"             => $order_arr['descs'],
                "gift_id"           => $order_arr["gift_id"],
                "gift"              => $order_arr["gift"],
                "order_id"          => $order_id,
                "operation_type"    => 1,
                "start_date_desc"   => "0" . " -> " . $order_arr["start_date"],
                "end_date_desc"     => "0" . " -> " . $order_arr["end_date"],
                "able_day_desc"     => "0" . " -> " . $order_arr["able_day"],
                "able_count_desc"   => "0" . " -> " . $order_arr['able_count'],
                "able_money_desc"   => "0" . " -> " . $order_arr['able_money']

            );

            db("gym_order_history")->insert($history_arr);
            //新增会员信息到ic设备中
            db("interface")->insert($member_arr);

            //记录员工业绩
            $this->employeeAchievement($gym_card["price"], $gym_member["member_ship_id"], $shop_id);

        }
        $new_start_row = $start_row + $limit;
//        $this->redirect("/cron/Index/test", array('sk' => '27615e64890e433c74e9eb34f974d07e', 'start_row' => $start_row + $limit, 'limit' => $limit)));
        $this->success("目前行数：" . ($new_start_row - 1), "/cron/Import/index?sk=27615e64890e433c74e9eb34f974d07e&start_row={$new_start_row}&limit = {$limit}", '', 1);
    }

    /*去掉所有空格*/
    private function trimAll($str)//删除空格
    {
        $qian=array(" ","　","\t","\n","\r");$hou=array("","","","","");
        return str_replace($qian,$hou,$str);
    }

    /**
     * 验证会员信息
     * @param $user
     * @param $cards
     * @return array
     */
    private function checkUser($user, $cards)
    {

        //会员卡号
        if (!preg_match("/^\d{1,10}$/", $user["会员卡号"])) { //卡号只能位10位数字
            return array("flag" => false, "msg" => "会员卡号");
        }

        //会员姓名
        if (empty($user['会员姓名']) || mb_strlen($user["会员姓名"] > 255)) {
            return array("flag" => false, "msg" => "会员姓名");
        }

        //会员类型
        if (empty($user['会员类型']) || empty($cards[$user["会员类型"]])) {
            return array("flag" => false, "msg" => "会员类型");
        }

        //加入时间
        if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $user['加入时间'], $parts))
        {
            //检测是否为日期
            if(!checkdate($parts[2],$parts[3],$parts[1]))
            {
                return array("flag" => false, "msg" => "加入时间");
            }
        } else {
            return array("flag" => false, "msg" => "加入时间");
        }

        //开卡日期
        if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $user['开卡日期'], $parts))
        {
            //检测是否为日期
            if(!checkdate($parts[2],$parts[3],$parts[1]))
            {
                return array("flag" => false, "msg" => "开卡日期");
            }
        } else {
            return array("flag" => false, "msg" => "开卡日期");
        }
        //到期日期
        if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $user['到期日期'], $parts))
        {
            //检测是否为日期
            if(!checkdate($parts[2],$parts[3],$parts[1]))
            {
                return array("flag" => false, "msg" => "到期日期");
            }
        } else {
            return array("flag" => false, "msg" => "到期日期");
        }
        //有效次数
        if(!is_numeric($user['有效次数'])) {
            return array("flag" => false, "msg" => "有效次数");
        }

        //卡内余额
        if (!is_numeric($user['卡内余额'])) {
            return array("flag" => false, "msg" => "卡内余额");
        }
        //移动电话
        if (!preg_match("/^1[3456789]\d{9}$/", $user['移动电话'])) {
            return array("flag" => false, "msg" => "移动电话");
        }
        //会员生日
        if (!empty($user['会员生日'])) {
            if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $user['会员生日'], $parts))
            {
                //检测是否为日期
                if(!checkdate($parts[2],$parts[3],$parts[1]))
                {
                    return array("flag" => false, "msg" => "会员生日");
                }
            } else {
                return array("flag" => false, "msg" => "会员生日");
            }
        }

        return array("flag" => true, "msg" => "验证通过");
    }

    /**
     * 获取唯一order_no
     * @return string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    private function getUniqueOrderNo() {
        $is_unique = false;
        while (!$is_unique) {
            $order_no = time() . rand(100,999);
            $is_exist = db("gym_order")->where(array("order_no" => $order_no))->find();
            if (empty($is_exist)) {
                $is_unique = true;
            }
        }
        return $order_no;
    }

    /**
     * 员工业绩记录
     * @param $employee_id
     * @param $money
     * @param int $shop_id
     */
    private function employeeAchievement($money, $employee_id, $shop_id = 0)
    {
        if (empty($employee_id)) {
            return false;
        }
        if (empty($shop_id)) {
            $shop_id = db("gym_employee")->where(array("id" => $employee_id))->value("shop_id");
        }
        $year_month = date("Ym", time());

        //查找是否存在该员工本月业绩记录
        $achievement = db("gym_employee_achievement")->where(array("employee_id" => $employee_id, "year_month" => $year_month))->find();
        if (empty($achievement)) {
            db("gym_employee_achievement")->insert(array("shop_id" => $shop_id, "employee_id" => $employee_id, "achievement" => $money, "year_month" => $year_month));
        } else {
            db("gym_employee_achievement")->where(array("id" => $achievement["id"]))->setInc('achievement', $money);
        }
        return true;
    }


    /**
     * 获取可用天数
     * @param $start
     * @param $end
     * @return float|int
     */
    private function getAbleDay($start,$end)
    {
        if(strcmp($start,$end)<0){
            if(strcmp(date("Y-m-d"),$start)<0){//未开卡
                $day=(strtotime("$end")-strtotime("$start"))/(24*60*60);
            }else{
                $day=(strtotime("$end")-strtotime(date("Y-m-d")))/(24*60*60);
            }
        }else{
            $day=0;
        }
        return $day;
    }
}
